package com.mc.dao.Impl;

import com.mc.bean.Teacher;
import com.mc.dao.TeacherDao;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lyx
 * 教师数据访问实现类
 */
//1加载驱动
//2创建数据库连接
//3创建SQL语句+预编译指令+设置指定参数
//4执行预编译指令，得到结果集
//5关闭连接
public class TeacherDaoImpl extends BaseDao implements TeacherDao {
    /**
     * 创建老师对象
     */
    private Teacher createTeacher(ResultSet rs) throws SQLException {
        Teacher teacher = new Teacher(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
                rs.getInt(5), rs.getString(6), rs.getString(7), rs.getInt(8));
        return teacher;
    }
    @Override
    public List<Teacher> findAll() {
        List<Teacher> list = new ArrayList<Teacher>();// 创建了一个集合对象
        try {
            regist();
            connection();
            String sql = "SELECT * FROM Teacher order by tid desc";
            getPst(sql);
            getRs();
            while (rs.next()) {
                Teacher teacher = createTeacher(rs);
                list.add(teacher);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    @Override
    public Teacher findByTid(int tid) {
        Teacher teacher = null;
        try {
            regist();
            connection();
            String sql = "select * from teacher where tid = ?";
            getPst(sql);
            pstmt.setInt(1, tid);
            getRs();
            while(rs.next()){
                teacher = createTeacher(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return teacher;
    }

    @Override
    public Teacher findByTelPass(String tel, String pass) {
        Teacher teacher = null;
        try {
            //1加载驱动
            regist();
            //2创建数据库连接
            connection();
            //3创建SQL语句+预编译指令+设置指定参数
            String sql = "select * from teacher where ttel = ? and tpassword = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, tel);
            pstmt.setString(2, pass);
            //4执行预编译指令，得到结果集
            getRs();
            while (rs.next()) {
                teacher = createTeacher(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5关闭连接
            close();
        }
        return teacher;
    }

    @Override
    public int add(Teacher teacher) {
        int i = 0;
        try {
            //1加载驱动
            regist();
            //2创建数据库连接
            connection();
            //3创建SQL语句+预编译指令+设置指定参数
            String sql = "insert into teacher (tname,ttel,tpassword,tsex,tage,theadurl,rid) values (?,?,?,?,?,?,?)";
            getPst(sql);
            pstmt.setString(1,teacher.getTname());
            pstmt.setString(2,teacher.getTtel());
            pstmt.setString(3,teacher.getTpassword());
            pstmt.setString(4,teacher.getTsex());
            pstmt.setInt(5,teacher.getTage());
            pstmt.setString(6,teacher.getTheadurl());
            pstmt.setInt(7,teacher.getRid());
            //4执行预编译指令，得到结果集
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭连接
            close();
        }
        return i;
    }

    @Override
    public int delete(int tid) {
        int i = 0;
        try {
            regist();
            connection();
            String sql = "delete from teacher where tid = ?";
            getPst(sql);
            pstmt.setInt(1, tid);
            i = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return i;
    }

    @Override
    public int update(Teacher teacher) {
        int i = 0;
        try {
            //1加载驱动
            regist();
            //2创建数据库连接
            connection();
            //3创建SQL语句+预编译指令+设置指定参数
            String sql = "update teacher set tname=?,ttel=?,tpassword=?,tage=?,tsex=?,"
                    + "theadurl=?,rid=? WHERE tid=?";
            getPst(sql);
            pstmt.setString(1, teacher.getTname());
            pstmt.setString(2, teacher.getTtel());
            pstmt.setString(3, teacher.getTpassword());
            pstmt.setInt(4, teacher.getTage());
            pstmt.setString(5, teacher.getTsex());
            pstmt.setString(6, teacher.getTheadurl());
            pstmt.setInt(7, teacher.getRid());
            pstmt.setInt(8, teacher.getTid());
            //4执行预编译指令，得到结果集
            i = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5关闭连接
            close();
        }
        return i;
    }

    @Override
    public Teacher findBytel(String tel) {
        Teacher teacher = null;
        try {
            regist();
            connection();
            String sql = "select * from teacher where ttel = ?";
            getPst(sql);
            pstmt.setString(1, tel);
            getRs();
            while(rs.next()){
                teacher = createTeacher(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return teacher;
    }
}
